package cn.dakaqi.activity.sign.services;

import cn.dakaqi.entities.ActivitySign;
import cn.dakaqi.utils.exception.ServiceRuntimeException;
import org.springframework.data.jpa.repository.JpaSpecificationExecutor;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.PagingAndSortingRepository;
import org.springframework.data.repository.query.Param;

/**
 * Created by chunyang on 2016/4/17.
 */
public interface ActivitySignDao extends PagingAndSortingRepository<ActivitySign, Long>, JpaSpecificationExecutor<ActivitySign>
{
    @Query(value = "select P from ActivitySign as P where volunteer.id =:volunteerId and activity.id =:activityId")
    ActivitySign queryByVolunteerAndActivity(@Param("volunteerId") Long volunteerId,@Param("activityId") Long activityId)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select sum(TIMES) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and VOLUNTEER_ID =:volunteerId")
    Long countByMember(@Param("volunteerId") Long volunteerId)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select sum(TIMES) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and VOLUNTEER_ID =:volunteerId and CREATE_TIME like :year%")
    Long countByMemberYear(@Param("volunteerId") Long volunteerId,@Param("year") String year)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select sum(TIMES) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and VOLUNTEER_ID =:volunteerId and CREATE_TIME like :month%")
    Long countByMemberMonth(@Param("volunteerId") Long volunteerId,@Param("month") String month)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select sum(TIMES) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and ACTIVITY_ID =:activityId")
    Long countByActivity(@Param("activityId") Long activityId)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select sum(TIMES) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and VOLUNTEER_ID =:volunteerId and CREATE_TIME between :startDate and :endDate")
    Long countByMemberAnd2Date(@Param("volunteerId") Long volunteerId,@Param("startDate") String startDate,@Param("endDate") String endDate)throws ServiceRuntimeException;

    @Query(nativeQuery = true,value = "select count(*) from U_ACTIVITY_SIGN where DEL_STATUS = 0 and VOLUNTEER_ID =:volunteerId and COMMNET_TYPE =:type")
    Long countComments(@Param("volunteerId") Long volunteerId, @Param("type") int type);
}
